from django.shortcuts import render,redirect,HttpResponse
import pymysql
from  SQL_connection import sqlheper
import json
import time
def classes(request):

    conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='123456',db='db66')
    cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("select id,title from class")
    class_list=cursor.fetchall()
    cursor.close()
    conn.close()
    return render(request,'classes.html',{'class_list':class_list})

def add_class(request):
    if request.method=="GET":
        return render(request,'add_class.html')
    else:
            v=request.POST.get('title')

            conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
            cursor.execute("insert into class(title) value (%s)",[v,])
            conn.commit()
            cursor.close()
            conn.close()
            return redirect('/classes/')
def del_class(request):
    nid=request.GET.get('nid')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("delete from class where id=%s", [nid, ])
    conn.commit()
    cursor.close()
    conn.close()
    return redirect('/classes/')

def edit_class(request):
    if request.method == "GET":
        nid = request.GET.get('nid')
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("select id,title from class where id=%s", [nid, ])
        #单个数据
        result=cursor.fetchone()
        cursor.close()
        conn.close()

        return render(request,'edit_class.html',{'result':result})
    else:
        nid=request.GET.get('nid')
        title=request.POST.get('title')

        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("update class set title=%s where id=%s ", [title,nid, ])
        # 单个数据
        conn.commit()
        cursor.close()
        conn.close()

        return redirect('/classes/')


def teacher_class(request):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("select * from class_teacher")
    class_list = cursor.fetchall()
    cursor.close()
    conn.close()

    return render(request,'techer_class.html',{'class_list':class_list})

def add_teacher_class(request):
    if request.method=='GET':
        return render(request,'add_teacher_class.html')
    else:
        v=request.POST.get('name')

        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("INSERT INTO class_teacher(teacher_name) VALUE(%s)",[v,])
        conn.commit()
        cursor.close()
        conn.close()
        return redirect('/teacher_class/')

def del_teacher_class(request):
    nid=request.GET.get('nid')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("delete from class_teacher where techer_id=%s ", [nid,])
    conn.commit()
    cursor.close()
    conn.close()
    return redirect('/teacher_class')
def edit_teacher_class(request):
    if request.method=='GET':
        nid = request.GET.get('nid')
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("SELECT techer_id,teacher_name FROM class_teacher WHERE techer_id=%s", [nid, ])
        result = cursor.fetchone()

        cursor.close()
        conn.close()
        return render(request, 'edit_teacher_class.html', {'result': result})

    else:
        nid=request.GET.get('nid')
        title=request.POST.get('techer_name')

        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("UPDATE class_teacher SET teacher_name=%s WHERE techer_id=%s ", [title,nid, ])
        conn.commit()
        cursor.close()
        conn.close()
        return redirect('/teacher_class/')

#查看学生信息和修改即删除
def students(request):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("SELECT student_id,class_id,student_name,title FROM class_student INNER JOIN class ON class_student.`class_id`=class.`id` ")
    result = cursor.fetchall()
    class_list=sqlheper.get_list('select id,title from class',[])
    return render(request,'students.html',{'result':result,'class_list':class_list})

def del_students(request):
    nid=request.GET.get('nid')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("delete from class_student where student_id=%s  ",[nid])
    conn.commit()

    return redirect('/students/')

def add_students(request):
    if request.method=="GET":
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("SELECT id,title FROM class")
        student_list = cursor.fetchall()
        cursor.close()
        conn.close()
        return render(request, 'add_students.html', {'student_list': student_list})
    else:
        title=request.POST.get('title')
        id=request.POST.get('class_id')
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db66')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("INSERT INTO class_student(student_name,class_id) VALUE(%s,%s)",[title,id])
        conn.commit()
        cursor.close()
        conn.close()
        return redirect('/students/')
#编辑学生
def edit_students(request):
    if request.method == "GET":
        nid = request.GET.get('nid')
        student_list = sqlheper.get_list("select id,title from class", [])
        result = sqlheper.get_one("select * from class_student where student_id=%s", [nid, ])
        return render(request, 'edit_students.html', {'students_list': student_list, 'result': result})
    else:
        nid=request.GET.get('nid')
        name=request.POST.get('title')
        class_id=request.POST.get('class_id')
        sqlheper.modify("UPDATE class_student SET student_name=%s,class_id=%s WHERE student_id=%s",[name,class_id,nid])
        return redirect('/students/')



def modal_add_class(request):
    title=request.POST.get('title')
    if len(title)>0:
        sqlheper.modify('insert into class(title) value(%s)',[title,])
        return HttpResponse('ok')
    else:
        return HttpResponse('班级标题不能为空')

def modal_edit_class(request):
    ret={'status':True,',message':None}
    try:
        nid=request.POST.get('nid')
        title=request.POST.get('title')
        sqlheper.modify('update class set title=%s where id=%s ',[title,nid])
    except:
        ret['status']=False
        ret['message']="处理异常"

    return HttpResponse(json.dumps(ret))
def modal_del_class(request):
    ret={'status':True,'message':None}

    try:
        title=request.POST.get('title')
        sqlheper.modify('delete from class where title=%s',[title,])
    except:
        ret['status']=False
        ret['message']="处理异常"

    return HttpResponse(json.dumps(ret))

#对话框添加学生
def modal_add_students(request):
    ret={'status':True,'message':None}
    try:
        class_id=request.POST.get('class_id')
        name=request.POST.get('name')
        sqlheper.modify('insert into class_student(student_name,class_id) value (%s,%s)',[name,class_id,])
    except Exception as e:
        ret['status']=False
        ret['message']=str(e)

    return HttpResponse(json.dumps(ret))

#对话框编辑学生
def modal_edit_students(request):

    ret = {'status': True, 'message': None}
    try:
        nid = request.POST.get('nid')
        name = request.POST.get('name')
        class_id=request.POST.get('class_id')
        sqlheper.modify('update class_student set student_name=%s,class_id=%s where student_id=%s',[name,class_id,nid,])

    except Exception as e:
        ret['status'] = False
        ret['message'] = str(e)

    return HttpResponse(json.dumps(ret))

def teachers(request):
    teacher_list=sqlheper.get_list("""
    SELECT teachers.`teacher_id` AS tid,title,class_teacher.`teacher_name` FROM class
    INNER JOIN  teachers ON teachers.`class_id`=class.`id`
    INNER JOIN class_teacher ON class_teacher.`techer_id`=teachers.`teacher_id`
    ORDER BY tid
    """,[])

    result={}

    for teacher in teacher_list:
        tid=teacher['tid']
        if tid in result:
             result[tid]['titles'].append(teacher['title'])
        else:
            result[tid]={'tid':teacher['tid'],'name':teacher['teacher_name'],'titles':[teacher['title'],]}

    return render(request,'teachers.html',{'teacher_list':result.values()})


def add_teacher(request):
    if request.method=="GET":
        class_list = sqlheper.get_list('select id,title from class ', [])
        return render(request, 'add_teacher.html', {'class_list': class_list})
    else:
        class_id=request.POST.getlist('class_id')
        teacher_name=request.POST.get('title')
        teacher_name_list=sqlheper.get_list('select teacher_name from class_teacher',[])
        teacher_name_list=[name['teacher_name'] for name in teacher_name_list]
        if teacher_name in teacher_name_list:
            class_list = sqlheper.get_list('select id,title from class ', [])
            return render(request,'add_teacher.html',{'class_list': class_list,'teacher_name':'输入姓名已存在请重新输入'})
        else:
            teacher_id = sqlheper.create('insert into class_teacher(teacher_name) value(%s)', [teacher_name, ])
            obj = sqlheper.SqlHelper()
            data_list = [(teacher_id, cls) for cls in class_id]
            obj.multiple_modify('insert into teachers(teacher_id,class_id) value (%s,%s)', data_list)
            obj.close()
            return redirect('/teachers/')

def del_teacher(request):
    nid=request.GET.get('nid')
    sqlheper.modify('DELETE FROM class_teacher WHERE techer_id=%s',[nid,])
    sqlheper.modify('DELETE FROM teachers WHERE teacher_id=%s',[nid])

    return redirect('/teachers/')

def edit_teacher(request):
    if request.method=="GET":
        nid = request.GET.get('nid')
        obj = sqlheper.SqlHelper()
        teacher_info = obj.get_one('select teacher_name,techer_id from class_teacher where techer_id=%s',[nid,])
        class_id_list = obj.get_list('select class_id from teachers where teacher_id=%s',[nid,])
        temp=[i['class_id'] for i in class_id_list]
        class_list = obj.get_list('select title,id from class',[])
        obj.close()

        return render(request,'edit_teacher.html',{
            'teacher_info':teacher_info,
            'class_id_list':temp,
            'class_list':class_list})
    else:
        nid=request.GET.get('nid')
        name=request.POST.get('name')
        class_ids=request.POST.getlist('class_ids')
        obj=sqlheper.SqlHelper()
        obj.modify('update class_teacher set teacher_name=%s where techer_id=%s',[name,nid,])
        obj.modify('delete from teachers where teacher_id=%s',[nid,])
        data_list = [(nid, cls) for cls in class_ids]
        obj.multiple_modify('insert into teachers(teacher_id,class_id) value (%s,%s)', data_list)
        obj.close()
        return redirect('/teachers/')


def get_all_class(request):
    time.sleep(3)
    obj=sqlheper.SqlHelper()
    class_list=obj.get_list('select id,title from class',[])

    obj.close()
    return HttpResponse(json.dumps(class_list))


def modal_add_teacher(request):
    ret={'status':True,'message':None}
    try:
        name=request.POST.get('name')
        class_id_list=request.POST.getlist('class_id_list')
        obj = sqlheper.SqlHelper()
        teacher_id=obj.create('insert into class_teacher(teacher_name) value(%s) ',[name,])
        data_list = [(teacher_id, cls) for cls in class_id_list]
        obj.multiple_modify('insert into teachers(teacher_id,class_id) value (%s,%s)', data_list)
        obj.close()
    except Exception as e:
        ret['message']="处理失败"
        ret['status']=False
    return HttpResponse(json.dumps(ret))


def get_class_id(request):
    teacher_id=request.GET.get('teacher_id')
    obj=sqlheper.SqlHelper()
    class_list_id = obj.get_list('select id,title from class', [])
    class_list=[]
    for cls in class_list_id:
        cls['status']=False
        class_list.append(cls)

    teacher_list_id=obj.get_list('SELECT class_id FROM teachers WHERE teacher_id=%s',[teacher_id,])
    teacher_ids=[teacher['class_id'] for teacher in teacher_list_id]
    class_list_id=[]

    for classe in class_list:
        if classe['id'] in teacher_ids:
            classe['status']=True
            class_list_id.append(classe)
        else:
            class_list_id.append(classe)

    obj.close()
    return HttpResponse(json.dumps(class_list_id))

def modal_edit_teacher(request):
    ret={'status':True,'message':None}
    try:
        name=request.POST.get('name')
        class_id_list=request.POST.getlist('class_id_list')
        nid=request.POST.get('nid')
        obj = sqlheper.SqlHelper()
        obj.modify('update class_teacher set teacher_name=%s where techer_id=%s', [name, nid, ])
        obj.modify('delete from teachers where teacher_id=%s', [nid, ])
        data_list = [(nid, cls) for cls in class_id_list]
        obj.multiple_modify('insert into teachers(teacher_id,class_id) value (%s,%s)', data_list)
        obj.close()
    except Exception as e:
        ret['status']=False
        ret['message']="处理异常"

    return HttpResponse(json.dumps(ret))

def loyout(request):
    return render(request,'loyout.html')
